package com.lisa.auto.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.lisa.auto.entity.ceramics.bo.BaseSingleBo;
import com.lisa.auto.entity.ceramics.po.CeramicsGoodsSinglePo;
import com.lisa.auto.entity.seveneleven.po.SevenElevenVehiclePo;
import com.lisa.auto.tools.ceramics.CeramicsComs;
import com.lisa.auto.tools.seveneleven.SevenElevenComs;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.tomcat.jni.FileInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author huangzhigang 2019/12/21
 */
public class ExcelUtils {

    static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

    public static String readExcel(MultipartFile file, String checkSheetName, List<String> cells) throws Exception {
        JSONArray jsonArray = new JSONArray();
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        List<Integer> cellIdxList = getCellIdx(workbook, checkSheetName, cells);
        List<Sheet> hssfSheetList = getSheet(workbook, checkSheetName);
        if (CollectionUtils.isEmpty(hssfSheetList)){
            return null;
        }
        for (Sheet hssfSheet : hssfSheetList){
            int rowNum = hssfSheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rowNum; i++) {
                JSONObject jsonObj = new JSONObject();
                Row row = hssfSheet.getRow(i);
                if (row == null){
                    continue;
                }
                for (int j = 0; j < cellIdxList.size(); j++) {
                    Cell hssfCell = row.getCell(cellIdxList.get(j));
                    if (hssfCell == null) {
                        continue;
                    }
                    String cellVal = getCellValue(hssfCell);
                    String cellTile = cells.get(j);
                    if ("TOTAL".equals(cellVal)){
                        continue;
                    }
                    if (StringUtils.isBlank(cellVal)){
                        cellVal = "";
                    }
                    if (("店铺".equals(cellTile) || "店号".equals(cellTile) || "外部店铺编号".equals(cellTile))){
                        if (StringUtils.isNotBlank(cellVal)){
                            jsonObj.put(cells.get(j), cellVal);
                            continue;
                        }
                    }
                    jsonObj.put(cells.get(j), cellVal);
                }
                jsonArray.add(jsonObj);
//                if ((jsonObj.containsKey("店铺") && RegexUtils.checkDigit(jsonObj.getString("店铺"))) || (jsonObj.containsKey("店号") && RegexUtils.checkDigit(jsonObj.getString("店号"))) ||
//                        (jsonObj.containsKey("外部店铺编号") && RegexUtils.checkDigit(jsonObj.getString("外部店铺编号")))){
//                    jsonArray.add(jsonObj);
//                }

            }
        }
        return jsonArray.toString();
    }

    public static List<Sheet> getSheet(Workbook workbook, String checkSheetName) {
        Sheet sheet = null;
        List<Sheet> sheetList = new ArrayList<>();
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            sheet = workbook.getSheetAt(i);
            CellReference ref = new CellReference("D1");
            for (int j = sheet.getNumMergedRegions() - 1; j >= 0; j--) {
                String value = "";
                CellRangeAddress region = sheet.getMergedRegion(j);
                Row firstRow = sheet.getRow(region.getFirstRow());
                Cell firstCellOfFirstRow = firstRow.getCell(region.getFirstColumn());
                //如果第一个单元格的是字符串
                if (firstCellOfFirstRow!=null && firstCellOfFirstRow.getCellType() == CellType.STRING) {
                    value = firstCellOfFirstRow.getStringCellValue();
                }

                //判断到C1才进行拆分单元格
                if(region.getFirstRow()==ref.getRow()&&region.getLastColumn()==ref.getCol()){
                    sheet.removeMergedRegion(j);
                }
                //设置第一行的值为，拆分后的每一行的值
                for (Row row : sheet) {
                    for (Cell cell : row) {
                        if (region.isInRange(cell.getRowIndex(), cell.getColumnIndex())){
                            cell.setCellValue(getCellValue(cell));
                        }
                    }
                }
            }
            String sheetName = sheet.getSheetName();
            if (StringUtils.isBlank(sheetName)){
                continue;
            }
            if (StringUtils.isNotBlank(sheetName) && sheetName.equals(checkSheetName)){
                sheetList.add(sheet);
                return sheetList;
            }
            if (StringUtils.isBlank(sheetName)){
                sheetList.add(sheet);
            }
        }
        return sheetList;
    }

    public static List<Integer> getCellIdx(Workbook workbook, String checkSheetName, List<String> cells) {
        List<Integer> cellIdxSet = new ArrayList<>();
        List<Sheet> hssfSheetList = getSheet(workbook, checkSheetName);
        if (CollectionUtils.isEmpty(hssfSheetList)) {
            return null;
        }
        for (Sheet hssfSheet : hssfSheetList){
            int rowNum = hssfSheet.getPhysicalNumberOfRows();
            for (int i = 0; i < rowNum; i++) {
                Row hssfRow = hssfSheet.getRow(i);
                if (hssfRow == null){
                    continue;
                }
                int cellSize = hssfRow.getPhysicalNumberOfCells();
                for (String cell : cells) {
                    for (int j = 0; j < cellSize; j++) {
                        Cell hssfCell = hssfRow.getCell(j);
                        if (hssfCell == null) {
                            continue;
                        }
                        String cellVal = getCellValue(hssfCell);
                        if (StringUtils.isBlank(cellVal)) {
                            continue;
                        }
                        String cellValue = cellVal.replaceAll(" ", "");
                        if (cell.equals(cellValue) && !cellIdxSet.contains(j)) {
                            cellIdxSet.add(j);
                            break;
                        }
                    }
                }
            }
        }
        return cellIdxSet;
    }


    /**
     * 读取Excel
     * @param inputStream 文件流
     * @param c 实体类Class
     * @return 根据excel表每行的记录生成的实体类list
     * @throws Exception
     */
    public static <T>List<T> readExcel(InputStream inputStream, Class<T> c, String param, Integer rowIdx) throws Exception {
        List<T> beanList = new ArrayList<T>();
        try {
            JSONArray jsonArray = new JSONArray();
            Map<Integer, String> resultMap = new HashMap<>();
            Workbook workbook = WorkbookFactory.create(inputStream);
            for (int idx = 0; idx < workbook.getNumberOfSheets(); idx++) {
                Sheet sheet = workbook.getSheetAt(idx);
                Row row = sheet.getRow(0);
                if (row == null) {
                    continue;
                }
                resultMap = getTitleCell(row, param);
                if (resultMap == null){
                    continue;
                }
                jsonArray = getBodyCellVal(sheet, 1, resultMap);
            }
            beanList = JSONArray.parseArray(jsonArray.toJSONString(), c);
        }catch (Exception e){
            LOGGER.info(e.getMessage(), e);
        }
        return beanList;
    }

    public static List<BaseSingleBo> readCeramicsExcel(InputStream inputStream, String excelHeader, String excelBody, boolean isHeader) throws Exception {
        try {
            List<BaseSingleBo> baseSingleList = new ArrayList<>();
            JSONObject paramObj = JSONObject.parseObject(excelHeader);
            Workbook workbook = WorkbookFactory.create(inputStream);
            for (int idx = 0; idx < workbook.getNumberOfSheets(); idx++) {
                List<CeramicsGoodsSinglePo> ceramicsGoodsList = new ArrayList<>();
                Sheet sheet = workbook.getSheetAt(idx);
                JSONObject jsonObject = new JSONObject();
                for (int i = 2 - 1; i >= 0; i--) {
                    Row sheetRow = sheet.getRow(i);
                    if (sheetRow == null){
                        continue;
                    }
                    for (int j = 0; j < sheetRow.getPhysicalNumberOfCells(); j++) {
                        Cell cell = sheetRow.getCell(j);
                        if (cell == null) {
                            continue;
                        }
                        String val = getCellValue(cell).replaceAll(SevenElevenComs.EMPTY_,SevenElevenComs.EMPTY);
                        if (StringUtils.isNotBlank(val) && paramObj.containsKey(val)){
                            jsonObject.put(paramObj.getString(val), getCellValue(sheetRow.getCell(j + 1)));
                            continue;
                        }
                    }
                }
                if (jsonObject.size() == 0){
                    continue;
                }
                ceramicsGoodsList = readExcel(sheet, CeramicsGoodsSinglePo.class, excelBody, 2);
                BaseSingleBo baseSingle = JSONObject.parseObject(jsonObject.toJSONString(), BaseSingleBo.class);
                baseSingle.setCeramicsGoodsPos(ceramicsGoodsList);
                baseSingleList.add(baseSingle);
            }
            return baseSingleList;
        }catch (Exception e){
            LOGGER.info(e.getMessage(), e);
        }
        return null;
    }

    public static <T>List<T> readExcel(Sheet sheet, Class<T> c, String param, Integer rowIdx) throws Exception {
        List<T> beanList = new ArrayList<T>();
        try {
            Row row = sheet.getRow(rowIdx==null?0:rowIdx);
            if (row == null) {
                return null;
            }
            Map<Integer, String> resultMap = getTitleCell(row, param);
            JSONArray jsonArray = getBodyCellVal(sheet, 3, resultMap);
            beanList = JSONArray.parseArray(jsonArray.toJSONString(), c);
        }catch (Exception e){
            LOGGER.info(e.getMessage(), e);
        }
        return beanList;
    }

    /**
     * 获取Excel 标题下标
     * @param row 行
     * @param param 参数集
     * @return Map<Integer, String>
     */
    public static Map<Integer, String> getTitleCell(Row row, String param){
        Map<Integer, String> resultMap = new HashMap<>();
        try {
            JSONObject paramObj = JSONObject.parseObject(param);
            for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                String val = getCellValue(cell);
                if (StringUtils.isNotBlank(val) && paramObj.containsKey(val.replaceAll(SevenElevenComs.EMPTY_,SevenElevenComs.EMPTY))){
                    resultMap.put(j, paramObj.getString(val.replaceAll(SevenElevenComs.EMPTY_, SevenElevenComs.EMPTY)));
                }
            }
            if (CollectionUtils.isEmpty(resultMap.keySet())){
                return null;
            }
            if (resultMap.keySet().size() < paramObj.keySet().size()){
                return null;
            }
        }catch (Exception e){
            LOGGER.info(e.getMessage(), e);
        }
        return resultMap;
    }

    /**
     * 解析excel内容
     * @param sheet Excel Sheet
     * @param rowIdx 行下标
     * @param cellMap 列名Map
     * @return
     */
    public static JSONArray getBodyCellVal(Sheet sheet, Integer rowIdx, Map<Integer, String> cellMap){
        JSONArray jsonArray = new JSONArray();
        try {
            int i = rowIdx;
            for (; i < sheet.getPhysicalNumberOfRows(); i++) {
                JSONObject jsonObject = new JSONObject();
                Row sheetRow = sheet.getRow(i);
                if (sheetRow == null){
                    continue;
                }
                if (cellMap == null){
                    continue;
                }
                for (int key : cellMap.keySet()){
                    Cell cell = sheetRow.getCell(key);
                    if (cell == null) {
                        continue;
                    }
                    jsonObject.put(cellMap.get(key), getCellValue(cell));
                }
                jsonArray.add(jsonObject);
            }
        }catch (Exception e){
            LOGGER.info(e.getMessage(), e);
        }
        return jsonArray;
    }


    public static void download(Workbook workbook, String title, HttpServletResponse response){
        try {
            OutputStream out = null;
            String headStr = "attachment; filename=\"" + new String( title.getBytes("gb2312"), "ISO8859-1" ) + "\"";
            response.setContentType("octets/stream");
            response.setContentType("APPLICATION/OCTET-STREAM");
            response.setHeader("Content-Disposition", headStr);
            out = response.getOutputStream();
            ExportExcelSeedBack.export(out, workbook);
            workbook.close();
            out.flush();
            out.close();
        }catch (Exception e){
            LOGGER.info(e.getMessage(), e);
        }
    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            LOGGER.error("[monitor][IO][表单功能]", e);
        }
    }

    /**
     * 获取单元格的值
     * @param cell
     */
    public static String getCellValue(Cell cell){
        CellType cellType = cell.getCellType();
        String cellValue;
        switch (cellType){
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case NUMERIC:
                cellValue = RegexUtils.strToNumber(String.valueOf(cell.getNumericCellValue()));
                break;
            default:
                cellValue = "0";
        }
        return cellValue;
    }


    /**
     * 陶瓷装车单转换
     * @param vehicleGroup 车次Excel数据
     * @param baseSingleGroup 原始单/订单
     * @param titleRow 标题数组
     * @param response
     */
    public static void excelExport(Map<String, List<SevenElevenVehiclePo>> vehicleGroup, Map<String, List<BaseSingleBo>> baseSingleGroup,
                              String[] titleRow, HttpServletResponse response) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        vehicleGroup.keySet().stream().forEach(plate->{
            List<SevenElevenVehiclePo> vehicles = vehicleGroup.get(plate);
            if (CollectionUtils.isEmpty(vehicles)){
                return;
            }
            Cell cell = null;
            Row row = null;
            Sheet sheet = workbook.createSheet(plate);
            sheet.setDefaultColumnWidth(26);
            sheet.setDefaultRowHeightInPoints(20);

            /** 设置字体-居中 */
            HSSFFont font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 14);

            HSSFCellStyle style = workbook.createCellStyle();
            /** 设置背景色 */
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            style.setFont(font);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);

            /** 单元格添加边框 */
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);

            int idx = 0;
            for (SevenElevenVehiclePo vehicle : vehicles){
                String compNum = vehicle.getShopId();
                String unloadingAddr = vehicle.getUnloadingAddr();
                if (StringUtils.isBlank(compNum)){
                    return;
                }
                for (int i = 0; i < CeramicsComs.HEADER_TITLE.length; i++) {
                    idx = idx + i;
                    Row sheetRow = sheet.createRow(idx);
                    Cell firstCell = sheetRow.createCell(0);
                    firstCell.setCellValue(CeramicsComs.HEADER_TITLE[i]);
                    firstCell.setCellStyle(style);
                    Cell secondCell = sheetRow.createCell(1);
                    secondCell.setCellValue(compNum);
                    if (i > 0) {
                        secondCell.setCellValue(unloadingAddr);
                    }
                    secondCell.setCellStyle(style);
                    CellRangeAddress cra = new CellRangeAddress(idx, idx, 1, 3);
                    sheet.addMergedRegion(cra);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
                    RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);

                }
                idx = idx + 1;
                Row title = sheet.createRow(idx);
                for(int i = 0;i < titleRow.length;i++){
                    cell = title.createCell(i);
                    cell.setCellValue(titleRow[i]);
                    cell.setCellStyle(style);
                }
                List<BaseSingleBo> dataList = baseSingleGroup.get(compNum);
                if (CollectionUtils.isEmpty(dataList)){
                    return;
                }
                for (int i = 0; i < dataList.size(); i++) {
                    BaseSingleBo baseSingle = dataList.get(i);
                    List<CeramicsGoodsSinglePo> ceramicsGoodsSingles = baseSingle.getCeramicsGoodsPos();
                    for (int j = 0; j < ceramicsGoodsSingles.size(); j++) {
                        CeramicsGoodsSinglePo ceramicsGoodsSingle = ceramicsGoodsSingles.get(j);
                        idx = idx + 1;
                        row = sheet.createRow(idx);
                        cell = row.createCell(0);
                        cell.setCellValue(ceramicsGoodsSingle.getBarCode());
                        cell.setCellStyle(style);

                        cell = row.createCell(1);
                        cell.setCellValue(ceramicsGoodsSingle.getGoodName());
                        cell.setCellStyle(style);

                        cell = row.createCell(2);
                        cell.setCellValue(ceramicsGoodsSingle.getGoodQuantity());
                        cell.setCellStyle(style);

                        cell = row.createCell(3);
                        cell.setCellValue(ceramicsGoodsSingle.getRemarks());
                        cell.setCellStyle(style);
                    }
                }
                idx = idx + 2;
            }
        });
        download(workbook, "陶瓷装车单"+DateUtil.format(DateUtil.DATE_TIME_1) + ".xls", response);
    }

}
